import sqlite3 from 'sqlite3';
import { fileURLToPath } from 'url';
import path from 'path';
import { promises as fs } from 'fs';

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

// 确保数据目录存在
const dbDir = path.join(__dirname, '../data');
try {
    await fs.mkdir(dbDir, { recursive: true });
} catch (error) {
    console.error('创建数据目录失败:', error);
}

const dbPath = path.join(dbDir, 'movies.db');

// 启用详细的调试日志
sqlite3.verbose();

// 创建数据库连接
const db = new sqlite3.Database(dbPath, sqlite3.OPEN_READWRITE | sqlite3.OPEN_CREATE, (err) => {
    if (err) {
        console.error('连接数据库失败:', err);
    } else {
        console.log('成功连接到数据库:', dbPath);
    }
});

// 将db.run等方法转换为Promise
const dbRun = (sql, params = []) => {
    return new Promise((resolve, reject) => {
        db.run(sql, params, function(err) {
            if (err) {
                console.error('SQL执行错误:', sql, err);
                reject(err);
            } else {
                resolve(this);
            }
        });
    });
};

const dbAll = (sql, params = []) => {
    return new Promise((resolve, reject) => {
        db.all(sql, params, (err, rows) => {
            if (err) {
                console.error('SQL查询错误:', sql, err);
                reject(err);
            } else {
                resolve(rows);
            }
        });
    });
};

const dbGet = (sql, params = []) => {
    return new Promise((resolve, reject) => {
        db.get(sql, params, (err, row) => {
            if (err) {
                console.error('SQL查询错误:', sql, err);
                reject(err);
            } else {
                resolve(row);
            }
        });
    });
};

// 初始化数据库表
const initDatabase = async () => {
    try {
        // 创建电影表
        await dbRun(`
            CREATE TABLE IF NOT EXISTS movies (
                id TEXT PRIMARY KEY,
                title TEXT NOT NULL,
                description TEXT,
                poster TEXT,
                category TEXT,
                rating REAL,
                releaseDate TEXT,
                dateAdded TEXT
            )
        `);

        // 创建分类表
        await dbRun(`
            CREATE TABLE IF NOT EXISTS categories (
                id TEXT PRIMARY KEY,
                name TEXT NOT NULL UNIQUE
            )
        `);

        // 创建管理员表
        await dbRun(`
            CREATE TABLE IF NOT EXISTS admins (
                id TEXT PRIMARY KEY,
                username TEXT NOT NULL UNIQUE,
                password TEXT NOT NULL,
                created_at TEXT DEFAULT CURRENT_TIMESTAMP
            )
        `);

        // 添加默认管理员账号
        const defaultAdmin = {
            id: '1',
            username: 'admin',
            // 这里使用一个默认密码，实际应用中应该使用更安全的密码
            password: 'admin123'
        };

        try {
            await dbRun(
                'INSERT INTO admins (id, username, password) VALUES (?, ?, ?)',
                [defaultAdmin.id, defaultAdmin.username, defaultAdmin.password]
            );
            console.log('默认管理员账号已创建');
        } catch (error) {
            // 如果插入失败（比如已存在），则忽略错误
            if (!error.message.includes('UNIQUE constraint failed')) {
                throw error;
            }
        }

        console.log('数据库表初始化成功');
    } catch (error) {
        console.error('初始化数据库失败:', error);
        throw error;
    }
};

// 导出数据库操作方法
export const database = {
    // 基础操作
    run: dbRun,
    all: dbAll,
    get: dbGet,
    init: initDatabase,
    
    // 电影相关操作
    async getMovies(page = 1, pageSize = 10, category = '') {
        try {
            // 构建基础查询
            let baseQuery = 'SELECT * FROM movies';
            let countQuery = 'SELECT COUNT(*) as total FROM movies';
            let queryParams = [];

            // 如果有分类筛选
            if (category && category !== '全部') {
                baseQuery += ' WHERE category = ?';
                countQuery += ' WHERE category = ?';
                queryParams.push(category);
            }

            // 添加排序和分页
            baseQuery += ' ORDER BY dateAdded DESC LIMIT ? OFFSET ?';
            const offset = (page - 1) * pageSize;
            queryParams.push(pageSize, offset);

            // 获取总记录数
            const totalResult = await dbGet(countQuery, category ? [category] : []);
            const total = totalResult.total;

            // 获取分页数据
            const movies = await dbAll(baseQuery, queryParams);

            return {
                movies,
                pagination: {
                    total,
                    page,
                    pageSize,
                    totalPages: Math.ceil(total / pageSize)
                }
            };
        } catch (error) {
            console.error('获取电影列表失败:', error);
            throw error;
        }
    },

    async getMovie(id) {
        return await dbGet('SELECT * FROM movies WHERE id = ?', [id]);
    },

    async addMovie(movie) {
        const { id, title, description, poster, category, rating, releaseDate, dateAdded } = movie;
        await dbRun(
            'INSERT INTO movies (id, title, description, poster, category, rating, releaseDate, dateAdded) VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
            [id, title, description, poster, category, rating, releaseDate, dateAdded]
        );
        return await this.getMovie(id);
    },

    async updateMovie(id, movie) {
        const { title, description, poster, category, rating, releaseDate } = movie;
        await dbRun(
            'UPDATE movies SET title = ?, description = ?, poster = ?, category = ?, rating = ?, releaseDate = ? WHERE id = ?',
            [title, description, poster, category, rating, releaseDate, id]
        );
        return await this.getMovie(id);
    },

    async deleteMovie(id) {
        await dbRun('DELETE FROM movies WHERE id = ?', [id]);
    },

    // 分类相关操作
    async getCategories() {
        return await dbAll('SELECT * FROM categories ORDER BY name');
    },

    async addCategory(category) {
        try {
            // 先检查分类是否已存在
            const existingCategory = await dbGet('SELECT * FROM categories WHERE name = ?', [category.name]);
            if (existingCategory) {
                console.log(`分类 ${category.name} 已存在，跳过添加`);
                return existingCategory;
            }

            const result = await dbRun(
                'INSERT INTO categories (id, name) VALUES (?, ?)',
                [category.id, category.name]
            );
            console.log(`成功添加分类: ${category.name}`);
            return { id: category.id, name: category.name };
        } catch (error) {
            console.error(`添加分类失败 ${category.name}:`, error);
            throw error;
        }
    },

    async deleteCategory(id) {
        await dbRun('DELETE FROM categories WHERE id = ?', [id]);
    },

    // 管理员相关操作
    async verifyAdmin(username, password) {
        try {
            const admin = await dbGet(
                'SELECT * FROM admins WHERE username = ? AND password = ?',
                [username, password]
            );
            return admin ? true : false;
        } catch (error) {
            console.error('验证管理员失败:', error);
            throw error;
        }
    },

    // 数据库关闭
    close() {
        return new Promise((resolve, reject) => {
            db.close((err) => {
                if (err) {
                    console.error('关闭数据库失败:', err);
                    reject(err);
                } else {
                    console.log('数据库已关闭');
                    resolve();
                }
            });
        });
    }
};

// 初始化数据库
await initDatabase();
